<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>MySQL &amp; Perl DBI</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="MySQL, Perl, DBI, tutorial, database, programming, development">
<meta name="description" content="In this chapter of the MySQL Perl tutorial, we 
present the Perl DBI module and the MySQL database.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>MySQL &amp; Perl DBI</h1>


<p>
In the first chapter of the MySQL Perl tutorial, we will introduce the
Perl DBI module and the MySQL database. We will provide some definitions 
and show how to install the necessary elements. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Prerequisites</h2>


<p>
To work with this tutorial, we must have Perl language, MySQL database,
Perl DBI and DBD::MySQL modules installed. The DBI is the standard Perl database 
interface. Each database has its driver. In our case, DBD::mysql is the driver 
for the MySQL database. 
</p>

<pre>
$ sudo perl -MCPAN -e shell
cpan> install DBI
cpan[2]> install DBD::mysql
</pre>

<p>
The above commands show, how to install Perl DBI and DBD::mysql
modules.
</p>

<h2>MySQL database</h2>

<p>
<b>MySQL</b> is a leading open source database management system. It is a multi user, 
multithreaded database management system. MySQL is especially popular on the web. 
It is one of the parts of the very popular <b>LAMP</b>
platform. Linux, Apache, MySQL, PHP. Currently MySQL is owned by Oracle.
MySQL database is available on most important OS
platforms. It runs under BSD Unix, Linux, Windows or Mac.
Wikipedia and YouTube use MySQL. These sites manage millions of queries
each day. MySQL comes in two versions. MySQL server system and MySQL
embedded system.
</p>

<p>
The MySQL comes with the mysql command line utility. It can be used to
issue SQL commands against a database. Now we are going to use the mysql 
command line tool to create a new database. 
</p>

<hr class="btm">

<pre>
$ sudo apt-get install mysql-server
</pre>

<p>
This command installs the MySQL server and various other packages. 
While installing the package, we are prompted to enter
a password for the MySQL root account. For installing MySQL from sources,
have a look at 
<a href="http://zetcode.com/databases/mysqltutorial/installation/">MySQL installation</a>
page.
</p>

<pre>
$ service mysql status
mysql start/running, process 1238
</pre>

<p>
We check if the MySQL server is running. If not, we need
to start the server.
</p>

<pre>
$ sudo service mysql start
</pre>

<p>
The above command is a common way to start MySQL if we have
installed the MySQL database from packages. 
</p>

<pre>
$ sudo -b /usr/local/mysql/bin/mysqld_safe
</pre>

<p>
The above command starts MySQL server using the MySQL server
startup script. The way how we start a MySQL server might
be different. It depends whether we have installed MySQL 
from sources or from packages and also on the Linux distro.
For further information consult 
<a href="http://zetcode.com/databases/mysqltutorial/firststeps/">MySQL first steps</a>
or your Linux distro information.
</p>

<hr class="btm">

<p>
Next, we are going to create a new database user and a new database. 
We use the mysql client.
</p>

<pre>
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)
</pre>

<p>
We use the <b>mysql monitor</b> client application to connect to the 
server. 
We connect to the database using the root account. We show all available
databases with the SHOW DATABASES statement. 
</p>

<pre>
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.02 sec)
</pre>

<p>
We create a new <b>mydb</b> database. We will use this database throughout 
the tutorial.
</p>

<pre>
mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';
Query OK, 0 rows affected (0.00 sec)

mysql> USE mydb;
Database changed

mysql> GRANT ALL ON mydb.* to user12@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye
</pre>

<p>
We create a new database user. We grant all privileges to this user
for all tables of the mydb database. 
</p>


<h2>Perl DBI</h2>

<p>
The Perl <b>DBI</b> (Database Interface) is a database access module for
the Perl programming language. It defines a set of methods, variables and
conventions that provide a standard database interface. The DBI is also 
responsible for the dynamic loading of drivers, error checking and handling, 
providing default implementations for methods, and many other non-database 
specific duties. The DBI dispatches method calls to the appropriate database 
driver. The <b>DBD</b> (Database Driver) is a Perl module which translates 
the DBI methods for a specific database engine. The database drivers are supplied by database
vendors. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my @ary = DBI->available_drivers();
print join("\n", @ary), "\n";
</pre>

<p>
The code example lists all available drivers on our system. 
</p>

<pre class="explanation">
use DBI;
</pre>

<p>
We import the DBI module for our script. 
</p>

<pre class="explanation">
my @ary = DBI->available_drivers();
</pre>

<p>
The <code>available_drivers()</code> class method gets all the 
current available drivers on our system.  
</p>

<pre class="explanation">
print join("\n", @ary), "\n";
</pre>

<p>
This line prints the drivers to the console, each on a separate
line. 
</p>

<pre>
$ ./available_drivers.pl 
DBM
ExampleP
File
Gofer
Proxy
SQLite
Sponge
mysql
</pre>

<p>
Example output.
</p>

<h2>Common DBI methods</h2>

<p>
The following table lists some common DBI methods.
</p>

<table>
<tr class="hdr"><th>Method name</th><th>Description</th></tr>
<tr><td>available_drivers()</td><td>Returns a list of all available drivers</td></tr>
<tr class="gray"><td>connect()</td><td>Establishes a connection to the requested data source</td></tr>
<tr><td>disconnect()</td><td>Disconnects from the database server</td></tr>
<tr class="gray"><td>prepare()</td><td>Prepares an SQL statement for execution</td></tr>
<tr><td>execute()</td><td>Executes the prepared statement</td></tr>
<tr class="gray"><td>do()</td><td>Prepares and executes an SQL statement</td></tr>
<tr><td>bind_param()</td><td>Associates a value with a placeholder in a prepared statement</td></tr>
<tr class="gray"><td>bind_col()</td><td>Binds a Perl variable to an output field of a SELECT statement</td></tr>
<tr><td>begin_work()</td><td>Starts a new transaction</td></tr>
<tr class="gray"><td>commit()</td><td>Writes the most recent series of uncommitted database changes to the database</td></tr>
<tr><td>rollback()</td><td>Undoes the most recent series of uncommitted database changes</td></tr>
<tr class="gray"><td>quote()</td><td>Quotes a string literal for use as a literal value in an SQL statement</td></tr>
<tr><td>dump_results()</td><td>Fetches all the rows and prints them</td></tr>
<tr class="gray"><td>fetchrow_array()</td><td>Fetches the next row as an array of fields</td></tr>
<tr><td>fetchrow_arrayref()</td><td>Fetches the next row as a reference array of fields</td></tr>
<tr class="gray"><td>fetchrow_hashref()</td><td>Fetches the next row as a reference to a hashtable</td></tr>
<tr><td>fetchall_arrayref()</td><td>Fetches all data as an array of arrays</td></tr>
<tr class="gray"><td>finish()</td><td>Finishes a statement and lets the system free resources</td></tr>
<tr><td>rows()</td><td>Returns the number of rows affected</td></tr>
<tr class="gray"><td>column_info()</td><td>Provides information about columns</td></tr>
<tr><td>table_info()</td><td>Provides information about tables</td></tr>
<tr class="gray"><td>primary_key_info()</td><td>Provides information about primary keys in tables</td></tr>
<tr><td>foreign_key_info()</td><td>Provides information about foreign keys in tables</td></tr>
</table>

<h2>Conventions</h2>

<p>
Perl programmers usually use the following variable names when working with Perl DBI. 
In this tutorial we will adhere to these conventions too. 
</p>

<table>
<tr class="hdr"><th>Variable name</th><th>Description</th></tr>
<tr><td>$dbh</td><td>Database handle object</td></tr>
<tr class="gray"><td>$sth</td><td>Statement handle object</td></tr>
<tr><td>$drh</td><td>Driver handle object (rarely seen or used in applications)</td></tr>
<tr class="gray"><td>$h</td><td>Any of the handle types above ($dbh, $sth, or $drh)</td></tr>
<tr><td>$rc</td><td>General Return Code  (boolean: true=ok, false=error)</td></tr>
<tr class="gray"><td>$rv</td><td>General Return Value (typically an integer)</td></tr>
<tr><td>@ary</td><td>List of values returned from the database, typically a row of data</td></tr>
<tr class="gray"><td>$rows</td><td>Number of rows processed (if available, else -1)</td></tr>
<tr><td>$fh</td><td>A filehandle</td></tr>
<tr class="gray"><td>undef</td><td>NULL values are represented by undefined values in Perl</td></tr>
<tr><td>\%attr</td><td>Reference to a hash of attribute values passed to methods</td></tr>
</table>


<p>
This chapter of the MySQL Perl tutorial was an introduction to the Perl DBI module and 
the MySQL database.
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified Al May, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

